﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;

namespace DAO
{
    public class TimKiemPhanCongDAO
    {
        public static List<TimKiemPhanCongDTO> TimKiem(string bienso, string tenTX, DateTime tuNgay, DateTime denNgay)
        {
          List<TimKiemPhanCongDTO> lstTimKiemPC = null;

          try
            {
                OleDbConnection cnn = BoKetNoiCSDL.KetNoi();
                string query = "";
                query += "SELECT NHAN_VIEN.TenNV, NHAN_VIEN.DiaChi, PHAN_CONG_XE_TAI_XE.BienSoXe, PHAN_CONG_XE_TAI_XE.ThoiGianBatDau, PHAN_CONG_XE_TAI_XE.ThoiGianKetThuc";
                query += " FROM ((NHAN_VIEN INNER JOIN TAI_XE ON NHAN_VIEN.MaNV = TAI_XE.MaNV) INNER JOIN PHAN_CONG_XE_TAI_XE ON TAI_XE.MaTaiXe = PHAN_CONG_XE_TAI_XE.MaTaiXe) INNER JOIN PHAN_CONG ON PHAN_CONG_XE_TAI_XE.MaPC = PHAN_CONG.MaPhanCong";
                query += " where ";

                if (bienso != "")
                {
                    query += "BienSoXe = '" + bienso + "' and ";
                }

                if (tenTX != "")
                {
                    query += "TenNV = '" + tenTX + "' and "; 
                }
                query += "NgayPhanCong between #" + tuNgay.ToShortDateString() + "# and #"  + denNgay.ToShortDateString() + "#";

                OleDbCommand cmd = new OleDbCommand(query, cnn);
                lstTimKiemPC = new List<TimKiemPhanCongDTO>();
               try
                {
                    OleDbDataReader reader = cmd.ExecuteReader();
                    int iStt = 0;
                    while (reader.Read())
                    {
                        iStt += 1;
                        TimKiemPhanCongDTO timKiemPCDto = new TimKiemPhanCongDTO();
                        timKiemPCDto.SoThuTu = iStt;
                        timKiemPCDto.TenTaiXe = (string)reader["TenNV"];
                        timKiemPCDto.DiaChi = (string)reader["DiaChi"];
                        timKiemPCDto.BienSoXe = (string)reader["BienSoXe"];
                        timKiemPCDto.NgayBatDau = (DateTime)reader["ThoiGianBatDau"];
                        timKiemPCDto.NgayKetThuc = (DateTime)reader["ThoiGianKetThuc"];
                        lstTimKiemPC.Add(timKiemPCDto);
                    }
                }
               catch
               {
                   throw new Exception("Lỗi đọc dữ liệu\n");
               }
               finally
               {
                   if (cnn.State == ConnectionState.Open)
                   {
                       cnn.Close();
                   }
                   cnn.Dispose();
               }

            }
          catch
          {
              throw new Exception("Không mở được kết nối CSDL");
          }
            return lstTimKiemPC;
        }

        public static List<TimKiemPhanCongDTO> LayDanhSachTaiXe()
        {
            List<TimKiemPhanCongDTO> lstDsTXe = null;
            OleDbConnection cnn = BoKetNoiCSDL.KetNoi();

            try
            {
                string query = "";
                query += "SELECT TAI_XE.MaTaiXe, NHAN_VIEN.TenNV, NHAN_VIEN.DiaChi";
                query += " FROM (NHAN_VIEN INNER JOIN TAI_XE ON NHAN_VIEN.MaNV = TAI_XE.MaNV)";

                OleDbCommand cmd = new OleDbCommand(query, cnn);
                lstDsTXe = new List<TimKiemPhanCongDTO>();

                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    TimKiemPhanCongDTO taiXe = new TimKiemPhanCongDTO();
                    taiXe.SoThuTu = (int)reader["MaTaiXe"];
                    taiXe.TenTaiXe = (string)reader["TenNV"];
                    taiXe.DiaChi = (string)reader["DiaChi"];
                    lstDsTXe.Add(taiXe);
                }

            }
            catch
            {
                throw new Exception("Lỗi đọc dữ liệu\n");
            }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
                cnn.Dispose();
            }

            return lstDsTXe;
        }
    }
}
